{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Window functions\n",
    "\n",
    "General Elections were held in the UK in 2015 and 2017. Every citizen votes in a constituency. The candidate who gains the most votes becomes MP for that constituency.\n",
    "\n",
    "All these results are recorded in a table ge\n",
    "\n",
    "yr\t| firstName\t| lastName\t| constituency\t| party\t| votes\n",
    "---:|-----------|-----------|---------------|-------|------:\n",
    "2015\t| Ian\t| Murray\t| S14000024\t| Labour\t| 19293\n",
    "2015\t| Neil\t| Hay\t| S14000024\t| Scottish National Party\t| 16656\n",
    "2015\t| Miles\t| Briggs\t| S14000024\t| Conservative | 8626\n",
    "2015\t| Phyl\t| Meyer\t| S14000024\t| Green\t| 2090\n",
    "2015\t| Pramod\t| Subbaraman\t| S14000024\t| Liberal Democrat\t| 1823\n",
    "2015\t| Paul\t| Marshall\t| S14000024\t| UK Independence Party\t | 601\n",
    "2015\t| Colin\t| Fox\t| S14000024\t| Scottish Socialist Party\t| 197\n",
    "2017\t| Ian\t| MURRAY\t| S14000024\t| Labour\t| 26269\n",
    "2017\t| Jim\t| EADIE\t| S14000024\t| SNP\t| 10755\n",
    "2017\t| Stephanie Jane Harley\t| SMITH\t| S14000024\t| Conservative\t| 9428\n",
    "2017\t| Alan Christopher\t| BEAL\t| S14000024\t| Liberal Democrats\t| 1388\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import findspark\n",
    "import pandas as pd\n",
    "findspark.init()\n",
    "\n",
    "SVR = '192.168.31.31'\n",
    "from pyspark.sql import SparkSession\n",
    "\n",
    "sc = (SparkSession.builder.appName('app09-') \n",
    "      .master(f'spark://{SVR}:7077') \n",
    "      .config('spark.sql.warehouse.dir', f'hdfs://{SVR}:9000/user/hive/warehouse') \n",
    "      .config('spark.cores.max', '4') \n",
    "      .config('spark.executor.instances', '1') \n",
    "      .config('spark.executor.cores', '2') \n",
    "      .config('spark.executor.memory', '10g') \n",
    "      .enableHiveSupport().getOrCreate())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Warming up\n",
    "\n",
    "Show the **lastName, party** and **votes** for the **constituency** 'S14000024' in 2017."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "ge = sc.read.table('sqlzoo.ge')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>lastname</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>BEAL</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1388</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>MURRAY</td>\n",
       "      <td>Labour</td>\n",
       "      <td>26269</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>EADIE</td>\n",
       "      <td>SNP</td>\n",
       "      <td>10755</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SMITH</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>9428</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  lastname              party  votes\n",
       "0     BEAL  Liberal Democrats   1388\n",
       "1   MURRAY             Labour  26269\n",
       "2    EADIE                SNP  10755\n",
       "3    SMITH       Conservative   9428"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ge.filter((ge['constituency']=='S14000024') & (ge['yr']==2017))\n",
    "    .select('lastname', 'party', 'votes')\n",
    "    .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Who won?\n",
    "\n",
    "You can use the RANK function to see the order of the candidates. If you RANK using (ORDER BY votes DESC) then the candidate with the most votes has rank 1.\n",
    "\n",
    "f**Show the party and RANK for constituency S14000024 in 2017. List the output by party**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "      <th>rank</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Conservative</td>\n",
       "      <td>9428</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Labour</td>\n",
       "      <td>26269</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1388</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>SNP</td>\n",
       "      <td>10755</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               party  votes  rank\n",
       "0       Conservative   9428     3\n",
       "1             Labour  26269     1\n",
       "2  Liberal Democrats   1388     4\n",
       "3                SNP  10755     2"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from pyspark.sql.functions import *\n",
    "from pyspark.sql import Window\n",
    "(ge.filter((ge['constituency']=='S14000024') & (ge['yr']==2017))\n",
    " .select('party', 'votes')\n",
    " .withColumn('rank', rank().over(Window.orderBy(col('votes').desc())))\n",
    " .orderBy('party')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. PARTITION BY\n",
    "\n",
    "The 2015 election is a different PARTITION to the 2017 election. We only care about the order of votes for each year.\n",
    "\n",
    "**Use PARTITION to show the ranking of each party in S14000021 in each year. Include yr, party, votes and ranking (the party with the most votes is 1).**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>yr</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "      <th>posn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2015</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>12465</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2017</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>21496</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2019</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>19451</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2015</td>\n",
       "      <td>Labour</td>\n",
       "      <td>19295</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2017</td>\n",
       "      <td>Labour</td>\n",
       "      <td>14346</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2019</td>\n",
       "      <td>Labour</td>\n",
       "      <td>6855</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2015</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1069</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2017</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1112</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2019</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>4174</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2015</td>\n",
       "      <td>SNP</td>\n",
       "      <td>23013</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>2019</td>\n",
       "      <td>SNP</td>\n",
       "      <td>24877</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>2015</td>\n",
       "      <td>UKIP</td>\n",
       "      <td>888</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "      yr              party  votes  posn\n",
       "0   2015       Conservative  12465     3\n",
       "1   2017       Conservative  21496     1\n",
       "2   2019       Conservative  19451     2\n",
       "3   2015             Labour  19295     2\n",
       "4   2017             Labour  14346     2\n",
       "5   2019             Labour   6855     3\n",
       "6   2015  Liberal Democrats   1069     4\n",
       "7   2017  Liberal Democrats   1112     3\n",
       "8   2019  Liberal Democrats   4174     4\n",
       "9   2015                SNP  23013     1\n",
       "10  2019                SNP  24877     1\n",
       "11  2015               UKIP    888     5"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ge.filter(ge['constituency']=='S14000021')\n",
    " .withColumn('posn', rank().over(\n",
    "     Window.partitionBy('yr').orderBy(col('votes').desc())))\n",
    " .select('yr', 'party', 'votes', 'posn')\n",
    " .orderBy('party', 'yr')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. Edinburgh Constituency\n",
    "\n",
    "Edinburgh constituencies are numbered S14000021 to S14000026.\n",
    "\n",
    "**Use PARTITION BY constituency to show the ranking of each party in Edinburgh in 2017. Order your results so the winners are shown first, then ordered by constituency.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>constituency</th>\n",
       "      <th>party</th>\n",
       "      <th>votes</th>\n",
       "      <th>posn</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S14000021</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>21496</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S14000022</td>\n",
       "      <td>SNP</td>\n",
       "      <td>18509</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>SNP</td>\n",
       "      <td>19243</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S14000024</td>\n",
       "      <td>Labour</td>\n",
       "      <td>26269</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S14000025</td>\n",
       "      <td>SNP</td>\n",
       "      <td>17575</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>18108</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>S14000021</td>\n",
       "      <td>Labour</td>\n",
       "      <td>14346</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>S14000022</td>\n",
       "      <td>Labour</td>\n",
       "      <td>15084</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>Labour</td>\n",
       "      <td>17618</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>S14000024</td>\n",
       "      <td>SNP</td>\n",
       "      <td>10755</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>S14000025</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>16478</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>SNP</td>\n",
       "      <td>15120</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>S14000021</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1112</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>S14000022</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1849</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>15385</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>S14000024</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>9428</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>S14000025</td>\n",
       "      <td>Labour</td>\n",
       "      <td>13213</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>Conservative</td>\n",
       "      <td>11559</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>2579</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>S14000024</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>1388</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>S14000025</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>2124</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>Labour</td>\n",
       "      <td>7876</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>Green</td>\n",
       "      <td>1727</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>SIR</td>\n",
       "      <td>132</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   constituency              party  votes  posn\n",
       "0     S14000021       Conservative  21496     1\n",
       "1     S14000022                SNP  18509     1\n",
       "2     S14000023                SNP  19243     1\n",
       "3     S14000024             Labour  26269     1\n",
       "4     S14000025                SNP  17575     1\n",
       "5     S14000026  Liberal Democrats  18108     1\n",
       "6     S14000021             Labour  14346     2\n",
       "7     S14000022             Labour  15084     2\n",
       "8     S14000023             Labour  17618     2\n",
       "9     S14000024                SNP  10755     2\n",
       "10    S14000025       Conservative  16478     2\n",
       "11    S14000026                SNP  15120     2\n",
       "12    S14000021  Liberal Democrats   1112     3\n",
       "13    S14000022  Liberal Democrats   1849     3\n",
       "14    S14000023       Conservative  15385     3\n",
       "15    S14000024       Conservative   9428     3\n",
       "16    S14000025             Labour  13213     3\n",
       "17    S14000026       Conservative  11559     3\n",
       "18    S14000023  Liberal Democrats   2579     4\n",
       "19    S14000024  Liberal Democrats   1388     4\n",
       "20    S14000025  Liberal Democrats   2124     4\n",
       "21    S14000026             Labour   7876     4\n",
       "22    S14000023              Green   1727     5\n",
       "23    S14000026                SIR    132     5"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ge.filter((ge['constituency'].between('S14000021', 'S14000026')) &\n",
    "       (ge['yr']==2017))\n",
    " .withColumn('posn', rank().over(\n",
    "     Window.partitionBy('constituency').orderBy(col('votes').desc())))\n",
    " .select('constituency', 'party', 'votes', 'posn')\n",
    " .orderBy('posn', 'constituency')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. Winners Only\n",
    "\n",
    "You can use [SELECT within SELECT](https://sqlzoo.net/wiki/SELECT_within_SELECT_Tutorial) to pick out only the winners in Edinburgh.\n",
    "\n",
    "**Show the parties that won for each Edinburgh constituency in 2017.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>constituency</th>\n",
       "      <th>party</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>S14000021</td>\n",
       "      <td>Conservative</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>S14000022</td>\n",
       "      <td>SNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>S14000023</td>\n",
       "      <td>SNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>S14000024</td>\n",
       "      <td>Labour</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>S14000025</td>\n",
       "      <td>SNP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>S14000026</td>\n",
       "      <td>Liberal Democrats</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  constituency              party\n",
       "0    S14000021       Conservative\n",
       "1    S14000022                SNP\n",
       "2    S14000023                SNP\n",
       "3    S14000024             Labour\n",
       "4    S14000025                SNP\n",
       "5    S14000026  Liberal Democrats"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ge.filter((ge['constituency'].between('S14000021', 'S14000026')) & (ge['yr']==2017))\n",
    " .withColumn('posn', rank().over(Window.partitionBy('constituency').orderBy(col('votes').desc())))\n",
    " .filter(col('posn')==1)\n",
    " .select('constituency', 'party')\n",
    " .orderBy('constituency')\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. Scottish seats\n",
    "\n",
    "You can use **COUNT** and **GROUP BY** to see how each party did in Scotland. Scottish constituencies start with 'S'\n",
    "\n",
    "**Show how many seats for each party in Scotland in 2017.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>party</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>SNP</td>\n",
       "      <td>34</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Labour</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Conservative</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Liberal Democrats</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               party  count\n",
       "0                SNP     34\n",
       "1             Labour      9\n",
       "2       Conservative     12\n",
       "3  Liberal Democrats      4"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(ge.filter((ge['constituency'].startswith('S')) & (ge['yr']==2017))\n",
    " .withColumn('posn', rank().over(\n",
    "     Window.partitionBy('constituency').orderBy(col('votes').desc())))\n",
    " .filter(col('posn')==1)\n",
    " .groupBy('party')\n",
    " .count()\n",
    " .toPandas())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "sc.stop()"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
